﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="LinkDB.cs" company="HelloWorld">
//   BY黄媛媛
// </copyright>
// <summary>
//   Defines the LinkDB type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace WindowsFormsApp2.Service
{
    using System;
    using System.Collections.Generic;
    using System.Data.SQLite;
    using System.Diagnostics.CodeAnalysis;

    using WindowsFormsApp2.Model;

    /// <summary>
    /// The link database.
    /// </summary>
    internal class LinkDB
    {
        /// <summary>
        /// The deadlines.
        /// </summary>
        [SuppressMessage("StyleCop.CSharp.MaintainabilityRules", "SA1401:FieldsMustBePrivate", Justification = "Reviewed. Suppression is OK here.")]
        public List<Deadline> Deadlines = Deadline.Deadlines;

        /// <summary>
        /// The courses.
        /// </summary>
        [SuppressMessage("StyleCop.CSharp.MaintainabilityRules", "SA1401:FieldsMustBePrivate", Justification = "Reviewed. Suppression is OK here.")]
        public List<Course> Courses = Course.Courses;

        /// <summary>
        /// The conn string.
        /// </summary>
        private const string ConnStr = @"data source=..\..\..\..\mfc.db;";

        /// <summary>
        /// The get dead lines.
        /// </summary>
        public void GetDeadLines()
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand();
                cmd.Connection = connection; // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText =
                    "CREATE TABLE IF NOT EXISTS courseDDL(ddlID int, courseID long,task string,deadline string)"; // 输入SQL语句
                cmd.ExecuteNonQuery(); // 调用此方法运行

                cmd.CommandText = "SELECT * FROM courseDDL";
                using (var sr = cmd.ExecuteReader())
                {
                    while (sr.Read())
                    {
                        try
                        {
                            var newDDL = new Deadline(sr.GetInt32(0), sr.GetInt64(1), sr.GetString(2), DateTime.Parse(sr.GetString(3)));
                            this.Deadlines.Add(newDDL);
                        }
                        catch (Exception)
                        {
                            // ddl名为纯数字
                            var newDDL = new Deadline(sr.GetInt32(0), sr.GetInt64(1), sr.GetInt64(2).ToString(), DateTime.Parse(sr.GetString(3)));
                            this.Deadlines.Add(newDDL);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// The add deadline.
        /// </summary>
        /// <param name="newDDL">
        /// The newDDL.
        /// </param>
        public void AddDeadline(Deadline newDDL)
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand
                              {
                                  Connection = connection,
                                  CommandText =
                                      "INSERT INTO courseDDL VALUES(" + newDDL.DDLId + ","
                                      + newDDL.CourseId + ",'" + newDDL.Task + "','"
                                      + newDDL.DeadlineDate.ToString("yyyy-MM-dd") + "')"
                              };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// The delete deadline.
        /// </summary>
        /// <param name="courseId">
        /// The course id.
        /// </param>
        /// <param name="ddlName">
        /// The deadline name.
        /// </param>
        public void DeleteDeadline(string courseId, string ddlName)
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand
                              {
                                  Connection = connection,
                                  CommandText =
                                      "DELETE FROM courseDDL WHERE courseID=" + courseId + " AND task='"
                                      + ddlName + "';"
                              };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                // 输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// The delete deadline by course id.
        /// </summary>
        /// <param name="courseId">
        /// The course id.
        /// </param>
        public void DeleteDDLByCourseId(string courseId)
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand
                              {
                                  Connection = connection,
                                  CommandText = "DELETE FROM courseDDL WHERE courseID=" + courseId + ";"
                              };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                // 输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// The get courses.
        /// </summary>
        public void GetCourses()
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand
                              {
                                  Connection = connection,
                                  CommandText =
                                      "CREATE TABLE IF NOT EXISTS schedule(courseID long,name string,weekday int,beginLesson int,lastLesson int)"
                              };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                // 输入SQL语句
                cmd.ExecuteNonQuery(); // 调用此方法运行

                cmd.CommandText = "SELECT * FROM schedule";
                using (var sr = cmd.ExecuteReader())
                {
                    while (sr.Read())
                    {
                        var newCourse = new Course(sr.GetInt64(0), sr.GetString(1), sr.GetInt32(2), sr.GetInt32(3), sr.GetInt32(4));
                        this.Courses.Add(newCourse);
                    }
                }
            }
        }

        /// <summary>
        /// The delete course.
        /// </summary>
        /// <param name="courseId">
        /// The course id.
        /// </param>
        public void DeleteCourse(string courseId)
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand
                              {
                                  Connection = connection,
                                  CommandText = "DELETE FROM schedule WHERE courseID=" + courseId + ";"
                              };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                // 输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// The add course.
        /// </summary>
        /// <param name="newCourse">
        /// The new course.
        /// </param>
        public void AddCourse(Course newCourse)
        {
            using (var connection = new SQLiteConnection(ConnStr))
            {
                connection.Open();
                var cmd = new SQLiteCommand { Connection = connection };

                // 把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = cmd.CommandText = "INSERT INTO schedule VALUES(" + newCourse.CourseId + ",'" + newCourse.Name + "'," + newCourse.Weekday
                                                    + "," + newCourse.BeginLesson + "," + newCourse.LastLesson + ")";
                cmd.ExecuteNonQuery();
            }
        }
    }
}
